package com.fh.util.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.fh.model.loan.CallLoanAssignExcelInfo;

public class AssignStartExcel {
	
	@SuppressWarnings({ "resource", "deprecation" })
	public void outputExcel(HttpServletResponse response,List<CallLoanAssignExcelInfo> list){
		
		OutputStream os = null;
		HSSFSheet sheet;
		response.reset();// 清空输出流
	    try {
			os=response.getOutputStream();
			String fileName="委外待催收";
		    response.setHeader("Content-disposition","attachment; filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");// 设定输出文件头
		    response.setContentType("application/msexcel");// EXCEL格式
		} catch (IOException e) {
			e.printStackTrace();
		}//取得输出流
	    
	    
	    HSSFWorkbook workbook=new HSSFWorkbook();
	    sheet = workbook.createSheet("sheet1");
	    
	    HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFFont headerFont = workbook.createFont();	//标题字体
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short)11);
		headerStyle.setFont(headerFont);
		
		short width = 20,height=25*20;
		sheet.setDefaultColumnWidth(width);
	    
		HSSFRow row = sheet.createRow(0);// 标题行
	    HSSFCell cell = row.createCell(0);// 单元格
	    cell.setCellStyle(headerStyle);//给标题加样式
	    row=sheet.createRow(0);
	    
	  //添加列表头
	    cell=row.createCell(0);
	    cell.setCellValue("借款人姓名");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(1);
	    cell.setCellValue("身份证号");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(2);
	    cell.setCellValue("借款人手机号");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(3);
	    cell.setCellValue("性别");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(4);
	    cell.setCellValue("产品名称");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(5);
	    cell.setCellValue("债务人类型");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(6);
	    cell.setCellValue("担保方式");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(7);
	    cell.setCellValue("借款金额");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(8);
	    cell.setCellValue("逾期天数");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(9);
	    cell.setCellValue("委托金额");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(10);
	    cell.setCellValue("逾期罚息");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(11);
	    cell.setCellValue("减免金额");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(12);
	    cell.setCellValue("贷款期限");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(13);
	    cell.setCellValue("放款日期");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(14);
	    cell.setCellValue("逾期起始日期");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(15);
	    cell.setCellValue("绑定银行卡号");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(16);
	    cell.setCellValue("绑定银行");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(17);
	    cell.setCellValue("借款编号");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(18);
	    cell.setCellValue("还款订单号");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(19);
	    cell.setCellValue("催收人员");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(20);
	    cell.setCellValue("联系人姓名1");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(21);
	    cell.setCellValue("借款人关系1");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(22);
	    cell.setCellValue("联系人电话1");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(23);
	    cell.setCellValue("联系人姓名2");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(24);
	    cell.setCellValue("借款人关系2");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(25);
	    cell.setCellValue("联系人电话2");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(26);
	    cell.setCellValue("联系人姓名3");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(27);
	    cell.setCellValue("借款人关系3");
	    cell.setCellStyle(headerStyle);
	    cell=row.createCell(28);
	    cell.setCellValue("联系人电话3");
	    cell.setCellStyle(headerStyle);
	    
	    
	    int count = list.size();
	    HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);;
	    for(int i=0;i<count;i++){
	    	HSSFRow rows=sheet.createRow(i+1);//创建数据列表行
	    	cell=rows.createCell(0);
	    	cell.setCellValue(list.get(i).getRealName());
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(1);
	    	cell.setCellValue(list.get(i).getContIdCard());
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(2);
	    	cell.setCellValue(list.get(i).getTelephone());
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(3);
	    	cell.setCellValue(list.get(i).getSex());//性别
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(4);
	    	cell.setCellValue(list.get(i).getProductName());//产品名称
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(5);
	    	cell.setCellValue(list.get(i).getBorrowPleType());//债务人类型
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(6);
	    	cell.setCellValue(list.get(i).getPromType());//担保方式
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(7);
	    	cell.setCellValue(list.get(i).getBorrowAmount());//借款金额
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(8);
	    	cell.setCellValue(list.get(i).getOverdueDate());//逾期天数
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(9);
	    	cell.setCellValue(list.get(i).getOverdueAmount());//委托金额
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(10);
	    	cell.setCellValue(list.get(i).getOverdueFee());//逾期罚息
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(11);
	    	cell.setCellValue(list.get(i).getYishiyong());//减免金额
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(12);
	    	cell.setCellValue(list.get(i).getDuration());//贷款期限
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(13);
	    	cell.setCellValue(list.get(i).getAddTime());//放款日期
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(14);
	    	cell.setCellValue(list.get(i).getOverdueStartDate());//逾期起始日期
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(15);
	    	cell.setCellValue(list.get(i).getToAccNo());//绑定银行卡号
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(16);
	    	cell.setCellValue(list.get(i).getToBankName());//绑定银行
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(17);
	    	cell.setCellValue(list.get(i).getBorrowNid());//借款编号
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(18);
	    	cell.setCellValue(list.get(i).getRepaymentId());//还款订单号
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	cell=rows.createCell(19);
	    	cell.setCellValue(list.get(i).getAssignCompany());//催收人员
	    	contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    	
	    	cell=rows.createCell(20);
	    	cell.setCellValue(list.get(i).getContUserName1());
	    	cell=rows.createCell(21);
	    	cell.setCellValue(list.get(i).getContShip1());
	    	cell=rows.createCell(22);
	    	cell.setCellValue(list.get(i).getContMobile1());
	    	cell=rows.createCell(23);
	    	cell.setCellValue(list.get(i).getContUserName2());
	    	cell=rows.createCell(24);
	    	cell.setCellValue(list.get(i).getContShip2());
	    	cell=rows.createCell(25);
	    	cell.setCellValue(list.get(i).getContMobile2());
	    	cell=rows.createCell(26);
	    	cell.setCellValue(list.get(i).getContUserName3());
	    	cell=rows.createCell(27);
	    	cell.setCellValue(list.get(i).getContShip2());
	    	cell=rows.createCell(28);
	    	cell.setCellValue(list.get(i).getContMobile3());
	    }
	    try {
	    	workbook.write(os);
		    os.flush();
		    os.close();
		} catch (Exception e) {
		}
		
	}

}
